{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "5f316197",
   "metadata": {},
   "source": [
    "通过不同矿物质的含量、颜色、酒精度、年份等来预测红酒的等级"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "ed903999",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "# 使用\"warnings\"模块可以对警告进行控制，包括忽略、输出到控制台、保存到日志文件等等\n",
    "import warnings\n",
    "warnings.filterwarnings('ignore')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "691b335f",
   "metadata": {},
   "source": [
    "1.读取数据查看数据的前5行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "325f3cfc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>type</th>\n",
       "      <th>alcohol</th>\n",
       "      <th>malic</th>\n",
       "      <th>ash</th>\n",
       "      <th>alcalinity</th>\n",
       "      <th>magnesium</th>\n",
       "      <th>phenols</th>\n",
       "      <th>flavanoids</th>\n",
       "      <th>nonflavanoids</th>\n",
       "      <th>proanthocyanins</th>\n",
       "      <th>color</th>\n",
       "      <th>hue</th>\n",
       "      <th>dilution</th>\n",
       "      <th>proline</th>\n",
       "      <th>particular year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>A</td>\n",
       "      <td>13.20</td>\n",
       "      <td>1.78</td>\n",
       "      <td>2.43</td>\n",
       "      <td>15.6</td>\n",
       "      <td>127.0</td>\n",
       "      <td>2.8</td>\n",
       "      <td>3.06</td>\n",
       "      <td>0.28</td>\n",
       "      <td>2.29</td>\n",
       "      <td>5.64</td>\n",
       "      <td>1.04</td>\n",
       "      <td>3.92</td>\n",
       "      <td>1065</td>\n",
       "      <td>22.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>A</td>\n",
       "      <td>13.20</td>\n",
       "      <td>1.78</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.38</td>\n",
       "      <td>1.05</td>\n",
       "      <td>3.4</td>\n",
       "      <td>1050</td>\n",
       "      <td>38.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>A</td>\n",
       "      <td>13.16</td>\n",
       "      <td>2.36</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.68</td>\n",
       "      <td>1.03</td>\n",
       "      <td>3.17</td>\n",
       "      <td>1185</td>\n",
       "      <td>26.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>A</td>\n",
       "      <td>14.37</td>\n",
       "      <td>1.95</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>7.80</td>\n",
       "      <td>0.86</td>\n",
       "      <td>3.45</td>\n",
       "      <td>1480</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>A</td>\n",
       "      <td>13.24</td>\n",
       "      <td>2.59</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.32</td>\n",
       "      <td>1.04</td>\n",
       "      <td>2.93</td>\n",
       "      <td>735</td>\n",
       "      <td>35.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  type  alcohol malic   ash  alcalinity  magnesium  phenols  flavanoids  \\\n",
       "0    A    13.20  1.78  2.43        15.6      127.0      2.8        3.06   \n",
       "1    A    13.20  1.78   NaN         NaN        NaN      NaN         NaN   \n",
       "2    A    13.16  2.36   NaN         NaN        NaN      NaN         NaN   \n",
       "3    A    14.37  1.95   NaN         NaN        NaN      NaN         NaN   \n",
       "4    A    13.24  2.59   NaN         NaN        NaN      NaN         NaN   \n",
       "\n",
       "   nonflavanoids  proanthocyanins  color   hue dilution  proline  \\\n",
       "0           0.28             2.29   5.64  1.04     3.92     1065   \n",
       "1            NaN              NaN   4.38  1.05      3.4     1050   \n",
       "2            NaN              NaN   5.68  1.03     3.17     1185   \n",
       "3            NaN              NaN   7.80  0.86     3.45     1480   \n",
       "4            NaN              NaN   4.32  1.04     2.93      735   \n",
       "\n",
       "   particular year  \n",
       "0             22.0  \n",
       "1             38.0  \n",
       "2             26.0  \n",
       "3             35.0  \n",
       "4             35.0  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('./wine3.csv')\n",
    "df.head(5)  #默认值就是前5行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "826d5a06",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(178, 15)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e86814bd",
   "metadata": {},
   "source": [
    "2.查看数据的缺失值百分比"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a6d3dbf1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type                0.000000\n",
       "alcohol             0.000000\n",
       "malic               1.685393\n",
       "ash                 2.247191\n",
       "alcalinity          2.247191\n",
       "magnesium           2.247191\n",
       "phenols            94.943820\n",
       "flavanoids          2.247191\n",
       "nonflavanoids       2.247191\n",
       "proanthocyanins     2.247191\n",
       "color               0.000000\n",
       "hue                 0.000000\n",
       "dilution            0.000000\n",
       "proline             0.000000\n",
       "particular year     1.685393\n",
       "dtype: float64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# df.isnull().sum()/len(df.index)*100\n",
    "df.isnull().sum()/df.shape[0]*100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c80d7a3c",
   "metadata": {},
   "source": [
    "3.遵循80%原则删除对应的缺失值过多的列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "be5e788f",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_drop = df.dropna(axis=1,thresh=df.shape[0]*0.8,inplace=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "5707abed",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(178, 14)"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_drop.shape #上面把phenols这列的缺失值进行删除了，可以用这个检测缺失值列是否被删除，是否还有15列，删除后应该只有14列了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "514f2930",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "type               0.000000\n",
       "alcohol            0.000000\n",
       "malic              1.685393\n",
       "ash                2.247191\n",
       "alcalinity         2.247191\n",
       "magnesium          2.247191\n",
       "flavanoids         2.247191\n",
       "nonflavanoids      2.247191\n",
       "proanthocyanins    2.247191\n",
       "color              0.000000\n",
       "hue                0.000000\n",
       "dilution           0.000000\n",
       "proline            0.000000\n",
       "particular year    1.685393\n",
       "dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_drop.isnull().sum()/df.shape[0]*100"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "db938ee2",
   "metadata": {},
   "source": [
    "4.对缺失值进行填充"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8d4b8ddd",
   "metadata": {},
   "source": [
    "进行缺失值填充时需要注意到数据的真实含义，更具含义选择填充方式，比如，字符类型的不能求均值，所以不能以均值填充"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "097bb1c4",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 178 entries, 0 to 177\n",
      "Data columns (total 14 columns):\n",
      " #   Column           Non-Null Count  Dtype  \n",
      "---  ------           --------------  -----  \n",
      " 0   type             178 non-null    object \n",
      " 1   alcohol          178 non-null    float64\n",
      " 2   malic            175 non-null    object \n",
      " 3   ash              174 non-null    float64\n",
      " 4   alcalinity       174 non-null    float64\n",
      " 5   magnesium        174 non-null    float64\n",
      " 6   flavanoids       174 non-null    float64\n",
      " 7   nonflavanoids    174 non-null    float64\n",
      " 8   proanthocyanins  174 non-null    float64\n",
      " 9   color            178 non-null    float64\n",
      " 10  hue              178 non-null    float64\n",
      " 11  dilution         178 non-null    object \n",
      " 12  proline          178 non-null    int64  \n",
      " 13  particular year  175 non-null    float64\n",
      "dtypes: float64(10), int64(1), object(3)\n",
      "memory usage: 19.6+ KB\n"
     ]
    }
   ],
   "source": [
    "df_drop.info() #查看每列的数据类型 发现type、malic、dilution列都是object类型，所以不能用均值填充"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f3b280bd",
   "metadata": {},
   "source": [
    "4.1.对特殊值进行填充 'E' 和 ' '两个为特殊异常值处理，它并不是缺失值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "1e9d1a8d",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将dilution这一列的为E值的替换成众数，众数可能有多个，所以带下标取第一个\n",
    "df_drop['dilution'] = df_drop['dilution'].replace('E',df_drop['dilution'].mode()[0])\n",
    "df_drop['dilution'] = df_drop['dilution'].astype(np.float64)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "b36b74bf",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将malic这一列的为‘ ’值的替换成众数，众数可能有多个，所以带下标取第一个\n",
    "df_drop['malic'] = df_drop['malic'].replace('    ',df_drop['malic'].mode()[0])\n",
    "df_drop['malic'] = df_drop['malic'].replace(' ',df_drop['malic'].mode()[0])\n",
    "df_drop['malic'] = df_drop['malic'].astype(np.float64)  #这里是空字符串，转不了float类型\n",
    "df_drop['malic'] = df_drop['malic'].replace(np.nan,df_drop['malic'].mode()[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "c094e427",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_drop['malic'].isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "1026de3c",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['type', 'alcohol', 'malic', 'ash', 'alcalinity', 'magnesium',\n",
       "       'flavanoids', 'nonflavanoids', 'proanthocyanins', 'color', 'hue',\n",
       "       'dilution', 'proline', 'particular year'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list_col = []\n",
    "for col in df_drop.columns:\n",
    "    if col != 'type' and col != 'malic' and col != 'dilution':\n",
    "        list_col.append(col)\n",
    "df_drop.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ad213a90",
   "metadata": {},
   "source": [
    "4.2.对标准的缺失值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a9328104",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.impute import SimpleImputer   #SimpleImputer适合用于缺失值的列数比较多的情况下\n",
    "\n",
    "model_mean = SimpleImputer(missing_values=np.nan,strategy='mean') #定义填充的规则 使用什么值进行填充,有均值、众数、中位数三种\n",
    "#方式1：这里太臃肿，用上面的循环把列都取出来放至一个列表list_col中\n",
    "# df_mean = model_mean.fit_transform(df_drop[['alcohol', 'ash', 'alcalinity', 'magnesium',\n",
    "#        'flavanoids', 'nonflavanoids', 'proanthocyanins', 'color', 'hue','proline', 'particular year']]) # 将规则应用到数据之上\n",
    "# 上面如果取连续的列可以用df.iloc[:, 0:2]来取，读取第1列到第2列（列的索引是从0开始的）\n",
    "# 方式2，把列放置在一个列表内\n",
    "# df_mean = model_mean.fit_transform(df_drop[list_col]) # 将规则应用到数据之上\n",
    "# 方式3，用列表的推导一起把循环、条件写了\n",
    "df_mean = model_mean.fit_transform(df_drop[[col for col in df_drop.columns if col!= 'type' and col!= 'malic' and col!= 'dilution']]) # 将规则应用到数据之上\n",
    "df_mean = pd.DataFrame(df_mean,columns=[col for col in df_drop.columns if col!= 'type' and col!= 'malic' and col!= 'dilution']) #转型"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "df01a935",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alcohol            0\n",
       "ash                0\n",
       "alcalinity         0\n",
       "magnesium          0\n",
       "flavanoids         0\n",
       "nonflavanoids      0\n",
       "proanthocyanins    0\n",
       "color              0\n",
       "hue                0\n",
       "proline            0\n",
       "particular year    0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mean.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "17b3859e",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并数据 把上面处理的缺失值进行合并\n",
    "df_mean['type'] = df_drop['type']\n",
    "df_mean['malic'] = df_drop['malic']\n",
    "df_mean['dilution'] = df_drop['dilution']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "f28bed2f",
   "metadata": {
    "collapsed": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "alcohol            0\n",
       "ash                0\n",
       "alcalinity         0\n",
       "magnesium          0\n",
       "flavanoids         0\n",
       "nonflavanoids      0\n",
       "proanthocyanins    0\n",
       "color              0\n",
       "hue                0\n",
       "proline            0\n",
       "particular year    0\n",
       "type               0\n",
       "malic              0\n",
       "dilution           0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_mean.isnull().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6a5a66fa",
   "metadata": {},
   "source": [
    "5.使用3σ原则检测并使用填充的方式对'ash'和'malic'列进行异常值处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "6e30fe83",
   "metadata": {},
   "outputs": [],
   "source": [
    "list_e = ['ash','malic']\n",
    "list_ev = []\n",
    "# 定义：其值(指的是这一列中的每一个值)与均值的差值的绝对值大于3倍标准差的值被定义为异常值\n",
    "# 公式：|x-μ|>3σ   ||：绝对值 μ：均值 σ：标准差\n",
    "for col in list_e:\n",
    "    while True:\n",
    "        mean = df_mean[col].mean()  #获取均值\n",
    "        std = df_mean[col].std() #获取标准差\n",
    "        for x in df_mean[col]:\n",
    "            if abs(x-mean) > 3*std:\n",
    "                print(x)\n",
    "                list_ev.append(x)\n",
    "                df_mean[col] = df_mean[col].replace(x,df_mean[col].mean())\n",
    "        if len(list_ev) == 0:\n",
    "            break\n",
    "        else:\n",
    "            list_ev.clear()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
